
//
//  SQLiteManager.swift
//  SQLite学习
//
//  Created by 鱼龙之梦 on 15/9/17.
//  Copyright © 2015年 panyanzhi. All rights reserved.
//

import Foundation

class SQLiteManager{
    //提供一个对外访问的单例对象，后续数据库相关的操作都依赖该句柄
    //不透明指针用来表示在Swift中不能表现的类型的指针，如不完整的结构类型
    //一个不透明指针的包装器
    /// A wrapper around an opaque C pointer.
    ///
    /// Opaque pointers are used to represent C pointers to types that
    /// cannot be represented in Swift, such as incomplete struct types.
    var db:COpaquePointer = nil
    static let sharedManager = SQLiteManager()
    func openDB(dbName:String){
        //存在沙盒的文档里
        let filePath = (NSSearchPathForDirectoriesInDomains(NSSearchPathDirectory.DocumentDirectory, NSSearchPathDomainMask.UserDomainMask, true).last! as NSString).stringByAppendingPathComponent(dbName)
        print(filePath)
        if sqlite3_open(filePath, &db) != SQLITE_OK {
            print("打开数据库失败")
            return
        }
        createTable() ? print("创建表格成功"):print("创建表格失败")
        
        //TODO：
    }
    //创建表
    func createTable()->Bool{
        //先加载文件
        let path = (NSBundle.mainBundle().bundlePath as NSString).stringByAppendingPathComponent("createTable.sql")
        let data = NSData(contentsOfFile: path)!
        let sql = String(data: data, encoding: NSUTF8StringEncoding)!
        //然后转换为Data数据
        //然后转换为字符串
        print(sql)
        return execSQL(sql)
    }
    //去执行SQL语句
    func execSQL(sql:String)->Bool{
        return sqlite3_exec(db, sql, nil, nil, nil) == SQLITE_OK
    }
    func execRercordSet(sql:String)->[AnyObject]?{
        //定义一个sql语句对象
        var stmt:COpaquePointer = nil
        if sqlite3_prepare_v2(db, sql, -1, &stmt, nil) != SQLITE_OK{
            print("sql语句编译失败")
            return nil
        }
        var list = [[String:AnyObject]]()
        while sqlite3_step(stmt) == SQLITE_ROW {
            let dict = execRecordDict(stmt)
            list.append(dict)
        }
        //释放操作语句
        sqlite3_finalize(stmt)
        return list
    }
    private func execRecordDict(stmt:COpaquePointer)->[String:AnyObject]{
        var dict = [String:AnyObject]()
        //获取结果集列数
        let colNum = sqlite3_column_count(stmt)
        for i in 0..<colNum{
            let cName = sqlite3_column_name(stmt, i)
            //获取每列字段
            let name = String(CString: cName, encoding: NSUTF8StringEncoding)!
            let type = sqlite3_column_type(stmt, i)
            switch type{
            case SQLITE_INTEGER:
                let iNum = Int(sqlite3_column_int64(stmt, i))
                dict[name] = iNum
            case SQLITE_FLOAT:
                dict[name] = sqlite3_column_double(stmt, i)
            case SQLITE_TEXT:
                let cStr = UnsafePointer<Int8>(sqlite3_column_text(stmt, i))
                dict[name] = String(CString: cStr, encoding: NSUTF8StringEncoding)
            case SQLITE_NULL:
                dict[name]=NSNull()
            default:
                print("不支持的数据类型")
            }
        }
        return dict
    }
    private let SQLITE_TRANSIENT = unsafeBitCast(-1, sqlite3_destructor_type.self)
    //盲目插入的处理
    func prepareExecSQL(sql:String,parmas:CVarArgType...)->Bool{
        //预编译 sql
        var stmt:COpaquePointer = nil
        if  sqlite3_prepare_v2(db, sql, -1, &stmt, nil) != SQLITE_OK{
            print("编译失败")
            return false
        }
        var result = true
        var index:Int32 = 1
        for obj in parmas{
            if obj is Int{
                sqlite3_bind_int64(stmt, index, Int64(obj as! Int))
            } else if obj is Double {
                sqlite3_bind_double(stmt, index, obj as! Double)
            }else if obj is String{
                let cstr = (obj as! String).cStringUsingEncoding(NSUTF8StringEncoding)!
                sqlite3_bind_text(stmt, index, cstr, -1, SQLITE_TRANSIENT)
            }else if obj is NSNull{
                sqlite3_bind_null(stmt, index)
            }else{
                print("数据类型错误！")
            }
            index++
        }
        //数据顺序批量操作
        if sqlite3_step(stmt) != SQLITE_DONE
        {
            result = false
            print("盲目插入失败")
        }
        //需要 对 sql 语句进行复位操作？？？？
        if sqlite3_reset(stmt) != SQLITE_OK{
            result = false
            print("复位语句错误")
        }
        sqlite3_finalize(stmt)
        return result
    }
    //回滚
    func beginTranscation()->Bool{
        let sql = "BEGIN TRANSACTION;"
        return execSQL(sql)
    }
    func commitTranscation()->Bool{
        let sql = "COMMIT TRANSACTION;"
        return execSQL(sql)
    }
    func rollBackTranscation() ->Bool{
        let sql = "ROLLBACK TRANSACTION;"
        return execSQL(sql)
    }
}